/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author L.E.S
 */
public class DBEquipment implements IFDBEquipment {

private  Connection con;

	public DBEquipment()
    {
      con = DBConnection.getInstance().getDBcon();
    }

	public int deleteEquipment(String barcode)
    {
        int rc=-1;

        String query="DELETE FROM product WHERE barcode = '" +
                        barcode + "'";
        System.out.println(query);
        try{ // delete from customer
                con.setAutoCommit(false);
                Statement stmt = con.createStatement();
                stmt.setQueryTimeout(5);
                rc = stmt.executeUpdate(query);
                stmt.close();
                con.commit();
                con.setAutoCommit(true);
        }//slut try
        catch(Exception ex){
                System.out.println("Delete exception in equipment db: "+ex);

                try{
                    rc=-1;
                    con.rollback();
                    con.setAutoCommit(true);
                }
                catch(Exception ex1)
                {
                    System.out.println("Error rolling back" + ex1);
                }
        }
        return(rc);
    }

	public Equipment findEquipment(String barcode, boolean retriveAssociation)
    {
        String wClause = "  product.barcode = '" + barcode + "'";
        return singleWhere(wClause, retriveAssociation);
    }

	public int updateEquipment(Equipment equip)
	{
		Equipment equipObj = equip;

		int rc = -1;

		String query = "UPDATE product SET "
				+ "name ='" + equipObj.getName() + "', "
				+ "purchasePrice =" + equipObj.getPurchasePrice() + ", "
				+ "salesPrice =" + equipObj.getSalesPrice() + ", "
				+ "rentPrice =" + equipObj.getRentPrice() + ", "
				+ "countryOfOrigin = '" + equipObj.getCountryOfOrigin() + "', "
				+ "minStock =" + equipObj.getMinStock() + ", "
				+ "maxStock =" + equipObj.getMaxStock() + ", "
				+ "currentStock =" + equipObj.getCurrentStock() + ", "
				+ "supplierID =" + equipObj.getSupplier().getSupplierID() + ", "
				+ " WHERE barcode = '" + equipObj.getBarCode() + "'";
		String query1 = "UPDATE equipment SET "
				+ "type ='" + equipObj.getType() + "', "
				+ "despriction ='" + equipObj.getDescription() + "', "
				+ " WHERE barcode = '" + equipObj.getBarCode() + "'";

		System.out.println("Update query:" + query + query1);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			rc = stmt.executeUpdate(query1);

			stmt.close();
		}
		catch (Exception ex)
		{
			System.out.println("Update exception in equipment db: " + ex);
		}
		return (rc);
	}

	public ArrayList<Equipment> getAllEquipment(boolean retriveAssociation)
	{
		return miscWhere("", retriveAssociation);
	}

	public int insertEquipment(Equipment equip)
    {
        //call to get the next Equipment Barcode
        int nextBarCode = GetMax.getMaxId("Select max(barCode) from Equipment");
        nextBarCode = nextBarCode + 1;
        System.out.println("next Equipment Barcode = " +  nextBarCode);

        int rc = -1;
	String query="INSERT INTO Product(barCode, name, purchasePrice, salesPrice, rentPrice, countryOfOrigin, minStock, maxStock, currentStock, supplierID)  VALUES('"+
	  	nextBarCode + "','" +
                equip.getName()  + "',"  +
				equip.getPurchasePrice()  + ","  +
                equip.getSalesPrice() + "," +
                equip.getRentPrice() + ",'" +
                equip.getCountryOfOrigin() + "'," +
				equip.getMinStock() + "," +
				equip.getMaxStock() + "," +
				equip.getCurrentStock() + "," +
				equip.getSupplier().getSupplierID() + ")";
	String query1="INSERT INTO Equipment(barcode, type, description)  VALUES('"+
	  	nextBarCode + "','" +
				equip.getType() + "','" +
				equip.getDescription() + "')";
                
       System.out.println("insert : " + query);
       try{ // insert new GunReplica
          con.setAutoCommit(false);
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
		  rc = stmt.executeUpdate(query1);
          stmt.close();
          con.commit();
          con.setAutoCommit(true);
       }//end try
       catch(SQLException ex){
          System.out.println("Equipment ikke oprettet");
          System.out.println("Insert exception in Equipment db: "+ex);
	  try{
             rc=-1;
             con.rollback();
             con.setAutoCommit(true);
          }
          catch(Exception ex1)
          {
             System.out.println("Error rolling back"+ ex1);
          }
       }
        return(rc);
    }

	public Equipment searchEquipmentName(String attValue, boolean retriveAssociation)
	{
		String wClause = "name like '%" + attValue + "%'";
		System.out.println("SearchEquipment " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}

	private Equipment singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Equipment equipObj = new Equipment();

	        String query =  buildQuery(wClause);
            System.out.println(query);
		try{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
                            equipObj = buildEquipment(results);
                            //assocaition is to be build
                            stmt.close();
                            if(retrieveAssociation)
                            {
								int supplierID = equipObj.getSupplier().getSupplierID();
                                DBSupplier dbSupplier = new DBSupplier();
                                Supplier supplier = dbSupplier.findSupplier(supplierID, false);
                                equipObj.setSupplier(supplier);
                                String countryCode = equipObj.getCountryOfOrigin();
                                DBCountry dbCount = new DBCountry();
                                String countryName = dbCount.getCountryName(countryCode);
                                equipObj.setCountryOfOrigin(countryName);
                            }
			}
                        else{
                            equipObj = null;
                        }
		}//end try
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return equipObj;
	}

	private ArrayList<Equipment> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Equipment> list = new ArrayList<Equipment>();
		String query = buildQuery(wClause);

		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next())
			{
				Equipment equipObj = new Equipment();
				equipObj = buildEquipment(results);
				list.add(equipObj);
			}

			stmt.close();

			if (retrieveAssociation)
			{
				DBSupplier DBsupplier = new DBSupplier();
				for (Equipment equipObj : list)
				{
						equipObj.setSupplier(DBsupplier.findSupplier(results.getInt("supplierID"), false));
						String countryCode = equipObj.getCountryOfOrigin();
						DBCountry dbCount = new DBCountry();
						String countryName = dbCount.getCountryName(countryCode);
						equipObj.setCountryOfOrigin(countryName);
				}
			}
		}
		catch (Exception e)
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private String buildQuery(String wClause)
	{
	    String query="SELECT product.barcode, name, purchasePrice, salesPrice, rentPrice, countryOfOrigin, minStock, maxStock, currentStock, supplierID, type, description  FROM product, equipment";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}

	private Equipment buildEquipment(ResultSet results)
      {   Equipment equipObj = new Equipment();
          try{ // the columns from the table product  are used
				equipObj.setBarCode(results.getString("barcode"));
                equipObj.setName(results.getString("name"));
                equipObj.setPurchasePrice(results.getFloat("purchasePrice"));
                equipObj.setSalesPrice(results.getFloat("salesPrice"));
                equipObj.setRentPrice(results.getFloat("rentPrice"));
				equipObj.setCountryOfOrigin(results.getString("countryOfOrigin"));
				equipObj.setMinStock(results.getInt("minStock"));
				equipObj.setMaxStock(results.getInt("maxStock"));
                equipObj.setCurrentStock(results.getInt("currentStock"));
				equipObj.setSupplier(new Supplier(results.getInt("supplierID")));
				equipObj.setType(results.getString("type"));
				equipObj.setDescription(results.getString("description"));
          }
         catch(Exception e)
         {
             System.out.println("error in building the Equipment object");
         }
         return equipObj;
      }
}
